package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.StudyProgress;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudyProgressMapper {
    @Select("SELECT " +
            "sp.target_subject_id, " +
            "ROUND((" +
            "   COALESCE(chapter_progress.progress, 0) * 0.3 + " +
            "   COALESCE(task_progress.progress, 0) * 0.2 + " +
            "   COALESCE(knowledge_progress.progress, 0) * 0.2 + " +
            "   COALESCE(exam_progress.progress, 0) * 0.3" +
            "), 2) AS progress_percentage, " +
            "COALESCE(chapter_progress.covered_chapters, 0) AS covered_chapters, " +
            "COALESCE(chapter_progress.total_chapters, 1) AS total_chapters, " +
            "COALESCE(task_progress.completed_tasks, 0) AS completed_tasks, " +
            "COALESCE(task_progress.total_tasks, 1) AS total_tasks, " +
            "COALESCE(knowledge_progress.mastered_points, 0) AS mastered_points, " +
            "COALESCE(knowledge_progress.total_points, 1) AS total_points, " +
            "COALESCE(exam_progress.avg_score, 0) AS avg_score " +
            "FROM study_plans sp " +

            // 章节进度计算
            "LEFT JOIN (" +
            "   SELECT " +
            "       sp.target_subject_id, " +
            "       COUNT(DISTINCT q.chapter_id) AS covered_chapters, " +
            "       (SELECT COUNT(DISTINCT chapter_id) FROM chapters WHERE subject_id = sp.target_subject_id) AS total_chapters, " +
            "       ROUND(COUNT(DISTINCT q.chapter_id) / GREATEST((SELECT COUNT(DISTINCT chapter_id) FROM chapters WHERE subject_id = sp.target_subject_id), 1) * 100, 2) AS progress " +
            "   FROM study_plans sp " +
            "   JOIN questions q ON q.subject_id = sp.target_subject_id " +
            "   JOIN answer_records ar ON ar.question_id = q.question_id AND ar.user_id = sp.user_id " +
            "   WHERE sp.user_id = #{userId} AND q.chapter_id IS NOT NULL " +
            "   GROUP BY sp.target_subject_id" +
            ") chapter_progress ON chapter_progress.target_subject_id = sp.target_subject_id " +

            // 任务进度计算
            "LEFT JOIN (" +
            "   SELECT " +
            "       sp.target_subject_id, " +
            "       COUNT(*) AS total_tasks, " +
            "       SUM(CASE WHEN dt.status = 'completed' THEN 1 ELSE 0 END) AS completed_tasks, " +
            "       ROUND(SUM(CASE WHEN dt.status = 'completed' THEN 1 ELSE 0 END) / GREATEST(COUNT(*), 1) * 100, 2) AS progress " +
            "   FROM daily_tasks dt " +
            "   JOIN study_plans sp ON dt.plan_id = sp.plan_id " +
            "   WHERE dt.user_id = #{userId} " +
            "   GROUP BY sp.target_subject_id" +
            ") task_progress ON task_progress.target_subject_id = sp.target_subject_id " +

            // 知识点掌握情况计算
            "LEFT JOIN (" +
            "   SELECT " +
            "       sp.target_subject_id, " +
            "       COUNT(DISTINCT kp.point_id) AS total_points, " +
            "       COUNT(DISTINCT CASE WHEN NOT EXISTS (" +
            "           SELECT 1 FROM wrong_questions wq " +
            "           JOIN question_knowledge qk ON qk.question_id = wq.question_id " +
            "           WHERE wq.user_id = sp.user_id AND qk.point_id = kp.point_id AND wq.wrong_count > 2" +
            "       ) THEN kp.point_id END) AS mastered_points, " +
            "       ROUND(COUNT(DISTINCT CASE WHEN NOT EXISTS (" +
            "           SELECT 1 FROM wrong_questions wq " +
            "           JOIN question_knowledge qk ON qk.question_id = wq.question_id " +
            "           WHERE wq.user_id = sp.user_id AND qk.point_id = kp.point_id AND wq.wrong_count > 2" +
            "       ) THEN kp.point_id END) / GREATEST(COUNT(DISTINCT kp.point_id), 1) * 100, 2) AS progress " +
            "   FROM study_plans sp " +
            "   JOIN knowledge_points kp ON kp.subject_id = sp.target_subject_id " +
            "   WHERE sp.user_id = #{userId} " +
            "   GROUP BY sp.target_subject_id" +
            ") knowledge_progress ON knowledge_progress.target_subject_id = sp.target_subject_id " +

            // 考试进度计算
            "LEFT JOIN (" +
            "   SELECT " +
            "       sp.target_subject_id, " +
            "       ROUND(AVG(COALESCE(pe.score, 0)), 2) AS avg_score, " +
            "       ROUND(AVG(COALESCE(pe.score, 0)) / 100 * 100, 2) AS progress " +
            "   FROM study_plans sp " +
            "   LEFT JOIN past_exam_records pe ON pe.user_id = sp.user_id " +
            "   LEFT JOIN exam_types et ON et.exam_type = pe.exam_type " +
            "   WHERE sp.user_id = #{userId} AND et.type_id = sp.target_subject_id " +
            "   GROUP BY sp.target_subject_id" +
            ") exam_progress ON exam_progress.target_subject_id = sp.target_subject_id " +

            "WHERE sp.user_id = #{userId} AND sp.status = 'active' " +
            "GROUP BY sp.target_subject_id")
    @Results({
            @Result(property = "targetSubjectId", column = "target_subject_id"),
            @Result(property = "progressPercentage", column = "progress_percentage"),
            @Result(property = "coveredChapters", column = "covered_chapters"),
            @Result(property = "totalChapters", column = "total_chapters"),
            @Result(property = "completedTasks", column = "completed_tasks"),
            @Result(property = "totalTasks", column = "total_tasks"),
            @Result(property = "masteredPoints", column = "mastered_points"),
            @Result(property = "totalPoints", column = "total_points"),
            @Result(property = "avgScore", column = "avg_score")
    })
    List<StudyProgress> selectStudyProgressByUserId(@Param("userId") Long userId);
}